package com.xxx.excel;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.atomic.AtomicBoolean;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.xxx.util.CommonUtil;

/**
 * Excel读取工具类
 */
public class ExcelRead {
	
	private static final Logger logger = LoggerFactory.getLogger(ExcelRead.class);
	
	private static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
	private static final String EMPTY = "";
	private static final String POINT = ".";
	
	// SimpleDateFormat非线程安全
    private static final ThreadLocal<SimpleDateFormat> threadLocal = new ThreadLocal<SimpleDateFormat>();
	
	public int totalRows; //sheet中总行数
	public int totalCells; //每一行总单元格数
	private ExcelRowReader rowReader; //业务处理器
	private boolean ifParsingResult; //是否直接返回解析结果 默认不返回结果是empty
	private AtomicBoolean isError=new AtomicBoolean(false); //数据处理是否出现异常等

	public boolean isIfParsingResult() {
		return ifParsingResult;
	}
	
	private ExcelRead(boolean ifParsingResult) {
		super();
		this.ifParsingResult = ifParsingResult;
	}
	
	/**
	 * 多实例对象
	 * 构造参数值为true：readExcel方法参数excelRecordQueue缓存上传记录
	 * 构造参数值为false：readExcel方法参数excelRecordQueue失效
	 * @param ifParsingResult
	 * @return
	 */
	public static ExcelRead getInstance(boolean ifParsingResult) {
		return new ExcelRead(ifParsingResult);
	}

	public ExcelRowReader getRowReader() {
		return rowReader;
	}
	public void setRowReader(ExcelRowReader rowReader) {
		this.rowReader = rowReader;
	}
	
	/**
	 * 读取excel
	 * @param file
	 * @param threadNum
	 * @param pagSize
	 * @param limitRowNum
	 * @param excelRecordQueue
	 * @throws Exception 
	 */
	public void readExcel(MultipartFile file, ExecutorService fixedThreadPool, int pagSize, int limitRowNum, 
			LinkedBlockingDeque<ArrayList<String>> excelRecordQueue) throws Exception {
		if((ifParsingResult&&excelRecordQueue==null)||fixedThreadPool==null||pagSize<=0) {
			throw new Exception("执行方法ExcelRead.readXlsx()发生异常: 请检查接口参数是否合法！ ");
		}
		
		if(file!=null&&!EMPTY.equals(file.getOriginalFilename().trim())){
			String suffix = CommonUtil.getSuffix(file.getOriginalFilename()); // excel文件后缀
			if(!EMPTY.equals(suffix)){
				InputStream input =null;
				try {// IO流读取文件
					input = file.getInputStream();
					if(OFFICE_EXCEL_2010_POSTFIX.equals(suffix)){
						XSSFWorkbook workbook = new XSSFWorkbook(input);// 创建文档
						readXlsx(pagSize, limitRowNum, workbook, fixedThreadPool, excelRecordQueue);
					}
					if(isError.get()) {
						throw new Exception("excel上传异常");
					}
				} catch (Exception e) {
					throw e;
				}finally {
					try {
						input.close();
					} catch (IOException e) {
						throw e;
					}
				}
			}
		}
	}
	
	/**
	 * 读取 Excel 2010 .xlsx
	 * @param pagSize
	 * @param limitRowNum
	 * @param workbook
	 * @param fixedThreadPool
	 * @param excelRecordQueue
	 * @throws Exception 
	 */
	private void readXlsx(int pagSize, int limitRowNum, XSSFWorkbook workbook, ExecutorService fixedThreadPool,
			LinkedBlockingDeque<ArrayList<String>> excelRecordQueue) throws Exception {
		// 截止sheet
		int stopSheetNum = rowReader.getSheetStopNum()==0 ? workbook.getNumberOfSheets() : rowReader.getSheetStopNum();
		
		//sheet页校验
		for(int numSheet=rowReader.getSheetStartNum();numSheet<stopSheetNum;numSheet++){
        	final int sheetIndex = numSheet;
        	XSSFSheet xssfSheet = workbook.getSheetAt(sheetIndex);
        	if(xssfSheet == null){
        		continue;
        	}
        	totalRows = xssfSheet.getLastRowNum();	//exce共10行且最后一行序号为10 这里显示totalRows=9
    		int startRowNum = rowReader.getStartRow();//起始行 默认0  //例如第7行为题目信息的开始     那么startRowNum=6  （6,12】 
    		int stopRowNum = rowReader.getStopRow()==0?totalRows:rowReader.getStopRow();
    		int processNum=stopRowNum-startRowNum+1;//4
    		if(processNum>limitRowNum) {
    			logger.error("sheet"+sheetIndex+"行数超过了"+limitRowNum+"行，上传终止");
    			isError.set(true);
    			return;
    		}
		}
		
		//多个sheet串行处理，每个sheet并发处理
        for(int numSheet=rowReader.getSheetStartNum(); numSheet<stopSheetNum; numSheet++){
        	final int sheetIndex = numSheet;
        	XSSFSheet xssfSheet = workbook.getSheetAt(sheetIndex);
        	if(xssfSheet == null){
        		continue;
        	}
        	totalRows = xssfSheet.getLastRowNum();	
    		int startRowNum = rowReader.getStartRow();
    		int stopRowNum = rowReader.getStopRow()==0?totalRows:rowReader.getStopRow();
    		int processNum=stopRowNum-startRowNum+1;//4
    		int num = (processNum-1)/pagSize+1;
    		
    		//并发处理
    		final CountDownLatch countDownLatch = new CountDownLatch(num);
    		for(int i=1; i<=num; i++) {
    			final int index =i;
    			fixedThreadPool.execute(new Runnable() {
    				public void run() {
    					int end = (index-1)*pagSize+startRowNum+pagSize;//当前页的截止行号
    					end =end<stopRowNum?end:stopRowNum;
    					if(end==stopRowNum) {
    						end++;
    					}
    		        	for(int start= (index-1)*pagSize+startRowNum; start<end;start++){
    		        		//检查是否有某个并发线程出现异常 需要终止当前线程
        		        	if(isError.get()) {
        		        		break;
        		        	}
    		        		XSSFRow xssfRow = xssfSheet.getRow(start);
    		        		if(xssfRow!=null){
    		        			ArrayList<String> rowInfo = new ArrayList<String>();
    		        			totalCells = xssfRow.getLastCellNum();//总单元格数量
    		        			int stopCellNum = rowReader.getStopCell()==0?totalCells:rowReader.getStopCell();//截止单元格
    		        			for(int c=rowReader.getStartCell();c<=stopCellNum;c++){
    		        				XSSFCell cell = xssfRow.getCell(c);
    		        				if(cell==null){
    		        					rowInfo.add(EMPTY);
    		        					continue;
    		        				}	        				
    		        				rowInfo.add(getXCellValue(cell).trim());
    		        			}
    		        			if(ifParsingResult) {
    		        				try {
	    		        				excelRecordQueue.add(rowInfo);
	    		        			} catch (Exception e) {
										logger.error(e.getMessage());
										isError.set(true);
										break;
									}
    		        			}else {
    		        				try {
										rowReader.getRows(sheetIndex, start, rowInfo, start==startRowNum);
										rowInfo.clear();//减轻内存压力
									} catch (Exception e) {
										logger.error(e.getMessage());
										isError.set(true);
										break;
									}
    		        			}
    		        		}
    		        	}
    		        	countDownLatch.countDown();
    				}
    			});
    		}
    		countDownLatch.await();
        }
	}
	
	/**
	 * 读取单元格内容
	 * @param xssfCell
	 * @return
	 */
	@SuppressWarnings("deprecation")
	private static String getXCellValue(XSSFCell xssfCell){
		 if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			 return String.valueOf(xssfCell.getBooleanCellValue());
		 } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			 String cellValue = "";
			 if(XSSFDateUtil.isCellDateFormatted(xssfCell)){
				 Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());
				 SimpleDateFormat sdf = threadLocal.get();
				 sdf = new SimpleDateFormat("yyyy/MM/dd");
				 cellValue = sdf.format(date);
			 }else{
				 DecimalFormat df = new DecimalFormat("#.##");
				 cellValue = df.format(xssfCell.getNumericCellValue());
				 String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
				 if(strArr.equals("00")){
					 cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
				 }	
			 }
			 return cellValue;
		 } else {
			return String.valueOf(xssfCell.getStringCellValue());
		 }
	}
	
}

/**
 * 自定义xssf日期工具类
 */
class XSSFDateUtil extends DateUtil {
	protected static int absoluteDay(Calendar cal, boolean use1904windowing) {  
        return DateUtil.absoluteDay(cal, use1904windowing);  
    } 
}